﻿/******************************************************    
文件名:TableImageDB.cs
版权  (c)  2010-11-23 淘渺圈圈工作室
作者:黄媛媛
创建日期:2010-12-03
维护人员:黄媛媛
维护日期:2010-12-03
文件功能描述:金属材料热处理网络学习平台_访问Image数据表操作
******************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;

namespace Mmht.DB
{
    public class TableImageDB
    {
        operate op = new operate();
        string sql = string.Empty;

        public void UploadImage(DataTable dt, int create_by, DateTime create_at)
        {
            Image im = new Image();
            im.created_by = username(create_by);
            im.created_at = create_at;
            operate op = new operate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                im.name = dt.Rows[i][0].ToString();
                im.url = dt.Rows[i][1].ToString();
                DelImage(im.name);
                sql = @"INSERT INTO image
                    (name,created_at,created_by,url,sortnum,show)
                     VALUES
                    ('" + im.name + "','" + im.created_at + "','" +
                        im.created_by + "','" + im.url + "','" + im.sortnum.ToString() + "','" + im.show.ToString() + "')";
                op.Execute(sql);
            }
        }

        private class Image
        {
            public string name, url, created_by;
            public DateTime created_at;
            public int sortnum = 0;
            public bool show = true;
        }

        private string username(int create_by)
        {
            sql = @"SELECT NAME FROM ADMIN WHERE ID='" + create_by.ToString() + "'";
            string yhm = string.Empty;
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            if (dt == null || dt.Rows.Count == 0)
            {
                yhm = "admin";
            }
            else
            {
                yhm = dt.Rows[0][0].ToString();
            }
            return yhm;
        }

        public DataRow GetImageById(int id)
        {
            sql = @"SELECT * FROM image WHERE ID='" + id.ToString() + "'";
            string yhm = string.Empty;
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            if (dt == null)
            {
                return null;
            }
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return dt.Rows[0];
        }

        public bool ChangeImageShow(int id)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "update image set show = 1-show where id = '" + id.ToString() + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool UpdateImageSortnum(int id, int num)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "update image set sortnum = " + num.ToString() + " where id = '" + id.ToString() + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool DelImageById(int id)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "delete from image where id='" + id.ToString() + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool DelImage(string name)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "delete from image where name='" + name + "'";
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool DelAllImage()
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "delete from image";
            bool bl = op.Execute(sql);
            return bl;
        }

        public bool CheckImageNameExist(string name)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = "select * from image where name='" + name + "'";
            DataTable dt = new DataTable();
            dt = op.Query(sql);
            if (dt == null)
            {
                return false;
            }
            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        // TODO  what's this?
        public DataTable UploadImageInfo(DateTime create_at)
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = @"SELECT name as 文件名,a.created_at as 上传时间,created_by as 用户名,url as 文件路径
                    FROM image a
                    WHERE a.created_at = '" + create_at.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
            return op.Query(sql);
        }

        public DataTable GetAllImages()
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = @"SELECT *
                    FROM image
                    ORDER BY sortnum";
            return op.Query(sql);
        }

        public DataTable GetAllActiveImages()
        {
            operate op = new operate();
            string sql = string.Empty;
            sql = @"SELECT *
                    FROM image a
                    WHERE a.show = 1 
                    ORDER BY sortnum";
            return op.Query(sql);
        }

    }
}
